package rai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import rai.dto.CourseDTO;
import rai.dto.GradeDTO;
import rai.dto.TakenCourseDTO;
import rai.form.EditTakenCourseForm;
import rai.util.Constants;

/**
 * Database operation for EditTakenCourse.
 *
 * @author Vu
 *
 */
public class EditTakenCourseInfoDAO {
    /** Log property. */
    private static Logger log = Logger
            .getLogger("EditTakenCourseInfoDAO.start");
    /**
     * Add new Course
     * @param empID
     * @param grade
     * @param courseID
     * @return
     */
    public String addCourse(int empID, String grade, int courseID){
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "select * from courseexp "
                    + "where EmpID=" + empID + " and CourseID=" + courseID;
            
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs=pStat.executeQuery(query);
            
            if(!rs.next()){
                query = "insert into courseexp "
                        + "value('" + courseID +"','" + empID +"',0x00,'" + grade + "')";
                conn = rai.util.Connection.getConnection();
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                
                
            } else {
                return "Data Exists in Database!!!";
            }
        } catch (final Exception sqlEx) {
            log.error("EditTakenProjectDAO.addPrj" + sqlEx);
        } finally {
            try {
                // rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenProjectDAO.addPrj " + sqlEx);
            }
        }
        log.debug("EditTakenProjectDAO.addPrj.end");
        return "Information Add Successfully!!!";
    }
    /**
     * Get course List
     * @return course List
     */
    public List<CourseDTO> getCourse() {
        CourseDTO dto;
        final List<CourseDTO> result = new ArrayList<CourseDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct CourseID,Name from course";
            // get connection
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            // execute query
            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {
                dto = new CourseDTO();
                dto.setId(rs.getInt("CourseID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getCourse " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getCourse " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Update course Info.
     *
     * @param editform
     * @param EmpID
     */
    public void updateCourse(final EditTakenCourseForm editform, final int EmpID) {
        log.info("EditTakenCourse.UpdateCourseInfo");
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        // ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "update course c set c.DateIssued='" + editform.getDate()
                    + "' where c.CourseID=" + editform.getId();
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            pStat.executeUpdate(query);
            query = "update courseexp ce set ce.Grade='" + editform.getGrade()
                    + "' where ce.CourseID=" + editform.getId()
                    + " and ce.EmpID=" + EmpID;
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            pStat.executeUpdate(query);

        } catch (final Exception sqlEx) {
            log.error("EditTakenCourseDAO.updateCourse " + sqlEx);
        } finally {
            try {
                // rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenCourseDAO.updateCourse " + sqlEx);
            }
        }
        log.debug("EditTakenCourseDAO.updateCourse.end");
    }

    /**
     * Get List Grade.
     * 
     * @return List GradeDTO
     */
    public List<GradeDTO> getGrade() {
        GradeDTO dto;
        final List<GradeDTO> result = new ArrayList<GradeDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            // query command
            query = "select distinct Grade from courseexp";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);

            // execute query
            rs = pStat.executeQuery();
            // get result

            while (rs.next()) {
                dto = new GradeDTO();
                dto.setGrade(rs.getString("Grade"));
                result.add(dto);
            }
            dto = new GradeDTO();
            dto.setGrade("Other");
            result.add(dto);

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getGrade " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getGrade " + sqlEx);
            }
        }

        return result;
    }

    /**
     * Get Course Info.
     *
     * @param CourseID
     * @param EmpID
     * @return
     */
    public TakenCourseDTO getCourse(final int CourseID, final int empID) {
        log.debug("EditTakenCourseInfoDAO.getCourse.start");
        final TakenCourseDTO prj = new TakenCourseDTO();
        prj.setId(CourseID);
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "select co.ComName, pe.Grade, p.Name \"CourseName\", "
            		+ "dp.DepartName \"DepartName\", Period, DateIssued, "
            		+ "p.Description"
                    + " from course p,department dp, "
                    + "courseexp pe, company co "
                    + "where pe.CourseID=p.CourseID and p.DepartID=dp.DepartID "
                    + "and co.CompanyID=dp.CompanyID and "
                    + "p.CourseID=" + CourseID
                    + " and pe.EmpID=" + empID;
            if (empID == 0) {
                query = "select co.ComName, p.Name \"CourseName\", "
                    + "dp.DepartName \"DepartName\", Period, DateIssued, "
                    + "p.Description"
                    + " from course p,department dp, "
                    + "courseexp pe, company co "
                    + "where pe.CourseID=p.CourseID and p.DepartID=dp.DepartID "
                    + "and co.CompanyID=dp.CompanyID and "
                    + "p.CourseID=" + CourseID;
                    
            }
            
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setCompany(rs.getString("ComName"));
                prj.setSname(rs.getString("CourseName"));
                prj.setDate(rs.getString("DateIssued"));
                prj.setPeriod(rs.getString("Period"));
                prj.setDes(rs.getString("Description"));
                prj.setDept(rs.getString("DepartName"));
                if (empID != 0) {
                    prj.setGrade(rs.getString("Grade"));
                }
            }
            query = "select GROUP_CONCAT(l.Name) \"Language\" "
                + "from pits_db.language l, course_language pl "
                + "where pl.LangID = l.LangID and pl.CourseID ="+prj.getId(); 
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setLang((rs.getString("Language")));
            }
            
            query = "select GROUP_CONCAT(l.Name) \"Database\" "
                + "from pits_db.database l, course_database pl "
                + "where pl.DatabaseID = l.DatabaseID and pl.CourseID ="+prj.getId(); 
            
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setDatabase((rs.getString("Database")));
            }
           
            query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                + "from pits_db.framework l, course_framework pl "
                + "where pl.FrameID = l.FrameID and pl.CourseID ="+prj.getId(); 
            
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setFrw((rs.getString(1)));
            }
          
            query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                + "from pits_db.technology l, course_technology pl "
                + "where pl.TechID = l.TechID and pl.CourseID ="+prj.getId(); 
            
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setFrw(prj.getFrw()+","+(rs.getString(1)));
            }
            
            query = "select GROUP_CONCAT(l.Name) \"Application\" "
                + "from pits_db.application l, course_application pl "
                + "where pl.AppID = l.AppID and pl.CourseID ="+prj.getId(); 
            
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setApp(prj.getFrw()+","+(rs.getString(1)));
            }
        } catch (final Exception sqlEx) {
            log.error("EditTakenCourseDAO.getCourse" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenCourseDAO.getCourse " + sqlEx);
            }
        }
        log.debug("EditTakenCourseDAO.getCourse.end");
        return prj;
    }

}
